# 通过读取两个本地文件，写入到MYSQL中
import json
from pymysql import Connection
class ues_file:
    def __ues_file_1(self)->list:
        f = open("./2011年1月销售数据.txt","r",encoding="UTF-8")
        f_read = f.readlines()
        res = f_read
        res_list = []
        for line in res:
            line_no_n = line.replace("\n","")
            line_list = line_no_n.split(",")
            res_list.append(line_list)
        return(res_list)
    def __ues_file_2(self)->list:
        res_list = []
        f = open("./2011年2月销售数据JSON.txt","r",encoding="UTF-8")
        f_read = f.readlines()
        for line in f_read:
            line_list = []
            f_list = json.loads(line)
            line_list.append(f_list["date"])
            line_list.append(f_list["order_id"])
            line_list.append(f_list["money"])
            line_list.append(f_list["province"])
            res_list.append(line_list)
        return res_list
    def go(self)->list[list]:
        f1 = self.__ues_file_1()
        f2 = self.__ues_file_2()
        return f1 + f2
go = ues_file()
file_msg = go.go()
conn = Connection(
    host = '127.0.0.1',
    port = 3306,
    user = 'root',
    password = '123456'
)
conn.select_db('py_sql')
cursor = conn.cursor()
for x in file_msg:
    sql = f"insert into orders(order_date,order_id,money,province) values('{x[0]}','{x[1]}',{x[2]},'{x[3]}');"
    #print(x)
    print(sql)
    cursor.execute(sql)
conn.commit()
conn.close()